package com.itjob.scott.lesson04;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import com.itjob.scott.annotation.ColumnMapper;

public class JDBCUtil {
	
	public static final String DBMS_ORACLE = "oracle.properties";
	
	private static final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	
	private static final String ORACLE_URL = "jdbc:oracle:thin:@{0}:{1}:{2}";

	private static final String HOST = "host";
	
	private static final String PORT = "port";
	
	private static final String DB_NAME = "dbName";

	static {
		try {
			Class.forName(ORACLE_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private static String getURL(Object... params) {
		return MessageFormat.format(ORACLE_URL, params);
	}
	
	public static Connection getConnection(String dbmsType) throws Exception{
		Properties option = new Properties();
		option.load(ClassLoader.getSystemResourceAsStream(dbmsType)); // JavaSE only 
		String host = option.getProperty(HOST);
		String port = option.getProperty(PORT);
		String dbName = option.getProperty(DB_NAME);
		
		return DriverManager.getConnection(getURL(host, port, dbName), option);
	}
	
	public static ResultSet query(Connection connection, String sql, Object... params) {
		try {
			PreparedStatement ps = connection.prepareStatement(sql);
			if(params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			
			return ps.executeQuery();
			
		} catch (SQLException e) {
			e.printStackTrace();
			
			return null;
		}
	}
	
	public static void insert(Connection connection, String sql, Object... params) {
		try {
			PreparedStatement ps = connection.prepareStatement(sql);
			if(params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void update(Connection connection, String sql, Object... params) {
		insert(connection, sql, params);
	}
	
	public static void delete(Connection connection, String sql, Object... params) {
		insert(connection, sql, params);
	}
	
	private static String getFieldTypeName(String simpleName) {
		if("int".equals(simpleName) || "Integer".equals(simpleName)) {
			return "Int";
		} else if("char".equals(simpleName) || "Character".equals(simpleName)) {
			return "String";
		}
		
		return simpleName;
	}
	
	public static Map<String, Object> call(Connection connection, String sql, List<CallParameter> callParameterList) throws SQLException {
		CallableStatement cs = connection.prepareCall(sql);
		List<CallParameter> outParamList = new ArrayList<CallParameter>();
		
		for (int i = 0; i < callParameterList.size(); i++) {
			CallParameter cp = callParameterList.get(i);
			
			if(cp.getMode() == CallParameter.IN || cp.getMode() == CallParameter.INOUT){
				cs.setObject(i + 1, cp.getValue());
			}
			
			if(cp.getMode() == CallParameter.INOUT || cp.getMode() == CallParameter.OUT){
				cs.registerOutParameter(i + 1, cp.getType());
				cp.setIndex(i + 1);
				outParamList.add(cp);
			}
		}
		
		cs.executeUpdate();
		
		if(!outParamList.isEmpty()) {
			Map<String, Object> outMap = new HashMap<String, Object>();
			for (CallParameter c : outParamList) {
				outMap.put(c.getName(), cs.getObject(c.getIndex()));
			}
			
			return outMap;
		}
		
		return null;
	}
	
	public static <T> List<T> getBeans(Class<T> clazz, ResultSet rs) throws Exception {
		List<T> list = new ArrayList<T>();
		
		while(rs.next()) {
			T t = clazz.newInstance();
			
			Field[] fields = clazz.getDeclaredFields();
			
			for (int i = 0; i < fields.length; i++) {
				Field f = fields[i];
				
				ColumnMapper mapper = f.getAnnotation(ColumnMapper.class);
				if(mapper == null) {
					continue;
				}
				
				String mapperValue = "".equals(mapper.value()) ? f.getName() : mapper.value();
				
				Method method = rs.getClass().getMethod("get" + getFieldTypeName(f.getType().getSimpleName()), String.class);
				method.setAccessible(true);
				Object value = method.invoke(rs, mapperValue);
				method.setAccessible(false);
				
				// Object value = rs.getString(mapper.value()); // ?
				
				f.setAccessible(true);
				f.set(t, value);
				f.setAccessible(false);
			}
			
			list.add(t);
		}
		
		return list;
	}
	
	public static void close(Connection connection, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static class CallParameter {
		
		public static final int IN = 1;
		public static final int OUT = 2;
		public static final int INOUT = 3;

		private String name;
		
		private Object value;
		
		private int mode; // 1:IN, 2:OUT, 3:INOUT
		
		private int type;
		
		private int index; // returnIndex
		
		public CallParameter() {
		}
		
		public CallParameter(String name, Object value, int mode, int type) {
			this.name = name;
			this.value = value;
			this.mode = mode;
			this.type = type;
		}

		public String getName() {
			return name;
		}

		public void setName(String name) {
			this.name = name;
		}

		public Object getValue() {
			return value;
		}

		public void setValue(Object value) {
			this.value = value;
		}

		public int getMode() {
			return mode;
		}

		public void setMode(int mode) {
			this.mode = mode;
		}

		public int getType() {
			return type;
		}

		public void setType(int type) {
			this.type = type;
		}

		public int getIndex() {
			return index;
		}

		public void setIndex(int index) {
			this.index = index;
		}
	}
}